import pymysql
import json
from decimal import Decimal
from datetime import datetime
import threading

update_lock = threading.Lock()

class DBConnection:
    def __init__(self):
        self.conn = None
        self._init_connection()
    
    def _init_connection(self):
        """初始化数据库连接"""
        self.conn = pymysql.connect(
            host='127.0.0.1',
            port=3306,
            user='root',
            password='123456',
            database='area',
            charset='utf8mb4'
        )
    
    def _ensure_connection(self):
        """确保连接有效，如果无效则重新连接"""
        try:
            # 检查连接是否有效
            self.conn.ping(reconnect=True)
        except:
            # 连接无效，重新初始化
            self._init_connection()
    
    def get_cursor(self, dict_cursor=False):
        """获取游标，自动确保连接有效"""
        self._ensure_connection()
        if dict_cursor:
            return self.conn.cursor(pymysql.cursors.DictCursor)
        return self.conn.cursor()
    
    def commit(self):
        """提交事务"""
        try:
            self.conn.commit()
        except:
            self.conn.rollback()
            raise
    
    def close(self):
        """关闭连接"""
        if self.conn:
            self.conn.close()
            self.conn = None

# 创建全局的数据库连接实例（长连接）
db = DBConnection()

def query_rows(sql, params):
    cursor = db.get_cursor(dict_cursor=True)
    try:
        cursor.execute(sql, params)
        rows = cursor.fetchall()
        rows = [convert_types(row) for row in rows]
        return rows
    finally:
        cursor.close()

def query_rows_as_json(sql, params):
    rows = query_rows(sql, params)
    return json.dumps(rows, ensure_ascii=False, indent=4, default=str)

def query_row(sql, params):
    cursor = db.get_cursor(dict_cursor=True)
    try:
        cursor.execute(sql, params)
        row = cursor.fetchone()
        if row:
            row = convert_types(row)
        return row
    finally:
        cursor.close()

def query_fields(sql, params):
    cursor = db.get_cursor(dict_cursor=True)
    try:
        cursor.execute(sql, params)
        row = cursor.fetchone()
        row = convert_types(row) if row else None
        if row:
            # Convert the row dictionary to a tuple of values
            fields = tuple(row.values())
        else:
            fields = ()
        return fields
    finally:
        cursor.close()

def update(sql, params):
    with update_lock:
        cursor = db.get_cursor()
        try:
            cursor.execute(sql, params)
            db.commit()
            return cursor.rowcount
        except:
            db.commit()  # 发生错误时回滚
            raise
        finally:
            cursor.close()
        
def batch_update(sql, params_list):
    if not params_list:
        return 0  # 空列表直接返回0
    
    cursor = db.get_cursor()
    try:
        # 使用executemany执行批量更新
        cursor.executemany(sql, params_list)
        db.commit()
        return cursor.rowcount
    except Exception as e:
        db.rollback()
        raise e  # 抛出异常以便上层处理
    finally:
        cursor.close()
        db.close()

def update_row(row_data):
    keys = list(row_data.keys())
    values = [row_data[key] for key in keys if key != 'obj_id']
    set_clause = ', '.join([f"{key}=%s" for key in keys if key != 'obj_id'])
    sql = f"UPDATE mi_obj SET {set_clause} WHERE obj_id=%s"
    params = values + [row_data['obj_id']]
    return update(sql, params)

def convert_types(row):
    if not row:
        return row
        
    for key, value in row.items():
        field_type = value.__class__.__name__ if value is not None else None
        if field_type == 'Decimal':
            row[key] = float(value)
        elif field_type in ['str', 'String']:
            row[key] = str(value)
        elif field_type in ['datetime', 'Timestamp']:
            if isinstance(value, datetime):
                row[key] = value.strftime('%Y-%m-%d %H:%M:%S')
            else:
                row[key] = str(value)
    return row
